#include "datamanger.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>


datamanger* datamanger::instance = nullptr;

datamanger::datamanger()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("data.db");
    if(!db.open())
    {
        //qDebug() << " 数据库打开失败" << db.lastError();
        return;
    }
    tableInit();
}

datamanger::~datamanger()
{
    if(db.isOpen())
    {
        db.close();
    }
}

void datamanger::tableInit()
{
    QString sqlStr = "create table if not exists UserInfo(name varchar(64) primary key,"
                     "pswd varchar(64) not null,online int);";
//                     "group text default '');";
    QSqlQuery query(sqlStr,db);
    if(!query.isActive())
    {
        //qDebug() << " 数据表初始化失败" << query.lastError();
    }
    query.exec("update UserInfo set online = 0;");
    query.exec("create table if not exists GroupInfo(groupName varchar(64) primary key,"
               "num int not null);");
    query.exec("create table if not exists FileInfo(filePath text primary key,"
               "fileName text not null,"
               "user text not null);");
    query.exec("create table if not exists MeetingInfo(id integer primary key,"
               "meetingName text not null,"
               "creator text not null,"
               "meetingIP text not null,"
               "meetingPort int not null,"
               "createTime text,"
               "finishTime);");
}

datamanger *datamanger::getInstance()
{
    if(instance == nullptr)
    {
        instance = new datamanger;
    }
    return instance;
}

void datamanger::removeInstance()
{
    if(instance!=nullptr)
    {
        delete instance;
    }
}

bool datamanger::regist(QString name, QString pswd)
{
    QSqlQuery query(db);
    query.prepare("insert into UserInfo values(:name,:pswd,0)");
    query.bindValue(":name",name);
    query.bindValue(":pswd",pswd);
    bool ret = query.exec();
    return ret;
}

int datamanger::login(QString name, QString pswd)
{
    QSqlQuery query(db);
    query.prepare("select * from UserInfo where name=:name and pswd=:pswd");
    query.bindValue(":name",name);
    query.bindValue(":pswd",pswd);
    query.exec();
    if(query.next())
    {
        if(query.value(2).toInt() == 1)
        {
            return -1;
        }
        else
        {
            setLoginStat(name,1);
            return 1;
        }
    }
    else
    {
        return 0;
    }
}

void datamanger::setLoginStat(QString user,int stat)
{
    QSqlQuery query(db);
    query.prepare("update UserInfo set online=:online where name=:name");
    query.bindValue(":name",user);
    query.bindValue(":online",stat);
    query.exec();
}

QStringList datamanger::getAllUsers()
{
    QStringList strs;
    QSqlQuery query(db);
    query.exec("select name from UserInfo;");
    while (query.next())
    {
        strs.push_back(query.value(0).toString());
    }
    return  strs;
}

bool datamanger::insertGroup(QStringList users, QString groupName)
{
    QSqlQuery query(db);
    query.prepare("insert into GroupInfo(groupName, num)values(:name,:num)");
    query.bindValue(":name",groupName);
    query.bindValue(":num",users.size());
    bool ret = query.exec();
    //qDebug() << "datamanger.cpp 117 : insertGroup " << query.lastError();
    if (ret)
    {
        insertGroupInfo(users, groupName);
        createOneGroup(users, groupName);
    }
    return ret;
}

void datamanger::alterGroupInfo(QString user)
{
    QSqlQuery query(db);
//    query.prepare("alter table GroupInfo add column :user varchar(64);");
//    query.bindValue(":user", user);
    query.exec(QString("alter table GroupInfo add column %1 varchar(64);").arg(user));
    //qDebug() << "datamanger.cpp 139 : alterGroupInfo " << query.lastError();
}

void datamanger::insertGroupInfo(QStringList users, QString groupName)
{
    for (auto user : users)
    {
        insertGroupInfo(user, groupName);
    }
}

bool datamanger::insertGroupInfo(QString user, QString groupName)
{
    QSqlQuery query(db);
//    query.prepare("update GroupInfo set :user = ':groupName' where groupName = ':group';");
//    query.bindValue(":user", user);
//    query.bindValue(":groupName", groupName);
//    query.bindValue(":group", groupName);
    bool ret = query.exec(QString("update GroupInfo set %1 = '%2' where groupName = '%3';").arg(user).arg(groupName).arg(groupName));
    //qDebug() << "datamanger.cpp 161 : insertGroupInfo " << query.lastError();
    return ret;
}

QStringList datamanger::selectGroup(QString user)
{
    QStringList strs;
    QSqlQuery query(db);
//    query.prepare("select :user from GroupInfo;");
//    query.bindValue(":user", user);
    query.exec(QString("select %1 from GroupInfo;").arg(user));
    //qDebug() << "datamanger.cpp 172 : selectGroup " << query.lastError();
    while (query.next())
    {
        QString s = query.value(0).toString();
        //qDebug() << s;
        if (s == "")continue;
        strs.push_back(s);
    }
    //qDebug() << "datamanger.cpp 172 : selectGroup ";
    return  strs;
}

void datamanger::createOneGroup(QStringList users, QString groupName)
{
    QSqlQuery query(db);
//    query.prepare("create table if not exists :group(users varchar(64) primary key);");
//    query.bindValue(":group", groupName);
    query.exec(QString("create table if not exists '%1'(users varchar(64) primary key);").arg(groupName));
    qDebug() << "datamanger.cpp 191 : createOneGroup " << query.lastError();
    insertOneGroupInfo(users, groupName);
}

QStringList datamanger::selectOneGroup(QString groupName)
{
    QStringList ret;
    QSqlQuery query(db);
//    query.prepare("select * from :group;");
//    query.bindValue(":group", groupName);
    query.exec(QString("select * from %1;").arg(groupName));
    //qDebug() << "datamanger.cpp 200 : selectOneGroup " << query.lastError();
    while (query.next())
    {
        ret.push_back(query.value(0).toString());
    }
    return ret;
}

void datamanger::insertOneGroupInfo(QStringList users, QString groupName)
{
    for (auto user : users)
    {
        insertOneGroupInfo(user, groupName);
    }
}

bool datamanger::insertOneGroupInfo(QString user, QString groupName)
{
    QSqlQuery query(db);
//    query.prepare("insert into :group values(:user);");
//    query.bindValue(":group", groupName);
//    query.bindValue(":user", user);
    bool ret = query.exec(QString("insert into %1 values('%2');").arg(groupName).arg(user));
    //qDebug() << "datamanger.cpp 224 : insertOneGroupInfo " << query.lastError();
    return ret;
}

bool datamanger::inviteGroupInfo(QString user, QString groupName)
{
    bool ret = insertGroupInfo(user, groupName);
    ret &= insertOneGroupInfo(user, groupName);
    if (!ret)
    {
        return ret;
    }
    int num = selectOneGroup(groupName).size();
    QSqlQuery query(db);
    query.prepare("update GroupInfo set num = :num where groupName = :groupName;");
    query.bindValue(":num", num);
    query.bindValue(":groupName", groupName);
    query.exec();
    //qDebug() << "datamanger.cpp 242 : inviteGroupInfo " << query.lastError();

    return  ret;
}

bool datamanger::insertFileInfo(QString filePath, QString fileName, QString user)
{
    QSqlQuery query(db);
    query.prepare("insert into FileInfo values(:path, :name, :user);");
    query.bindValue(":path", filePath);
    query.bindValue(":name", fileName);
    query.bindValue(":user", user);
    bool ret = query.exec();
    //qDebug() << "datamanger.cpp 255 : insertFileInfo " << query.lastError();
    return ret;
}

QStringList datamanger::selectFileName()
{
    QStringList ret;
    QSqlQuery query(db);
    query.exec("select fileName from FileInfo;");
    //qDebug() << "datamanger.cpp 265 : selectFileName " << query.lastError();
    while (query.next())
    {
        ret.push_back(query.value(0).toString());
    }
    return ret;
}

QString datamanger::selectFilePath(QString fileName)
{
    QSqlQuery query(db);
    query.exec(QString("select filePath from FileInfo where fileName = '%1';").arg(fileName));
    //qDebug() << "datamanger.cpp 275 : selectFilePath " << query.lastError();
    query.next();
    return query.value(0).toString();
}

void datamanger::insertMeetingInfo(QString meetingName, QString creator, QString ip, quint16 port, QString time)
{
    QSqlQuery query(db);
    query.prepare("insert into MeetingInfo(meetingName, creator, meetingIP, meetingPort, createTime) "
                  "values(:meetingName, :creator, :ip, :port, :time);");
    query.bindValue(":meetingName", meetingName);
    query.bindValue(":creator", creator);
    query.bindValue(":ip", ip);
    query.bindValue(":port", port);
    query.bindValue(":time", time);
    query.exec();
    //qDebug() << "datamanger.cpp 300 : insertMeetingInfo " << query.lastError();
}

